package com.jww.dao.bill;

import com.jww.dao.BaseDao;
import com.jww.pojo.Bill;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BillDaoImpl implements BillDao {

    // 计算订单的数量，（根据产品名称和提供id）
    @Override
    public int getBillCount(Connection conn, String productName, int providerId, int isPayment) throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        int count = 0;
        if (conn != null) {
            StringBuffer sql = new StringBuffer();
            sql.append("select count(1) as count\n" +
                    "from smbms_bill b,smbms_provider p\n" +
                    "where b.providerId = p.id");
            // 存放我们的参数
            ArrayList<Object> list = new ArrayList<>();

            if (productName != null) {
                sql.append(" and b.productName like ?");
                list.add("%" + productName + "%"); // 模糊查询
            }

            if (providerId != 0) {
                sql.append(" and b.providerId = ?");
                list.add(providerId);
            }

            if (isPayment != 0) {
                sql.append(" and b.isPayment = ?");
                list.add(isPayment);
            }

            // 把list转换为数组
            Object[] params = list.toArray();
            // 输出完整sql
            System.out.println("BillDaoImpl-->:" + sql.toString());

            rs = BaseDao.execute(conn, ps, rs, sql.toString(), params);

            if (rs.next()) {
                count = rs.getInt("count");
            }
            // 关闭资源
            BaseDao.closeResource(null, ps, rs);
        }
        return count;
    }

    // 查询订单列表信息
    @Override
    public List<Bill> getBillList(Connection conn, String productName, int providerId, int isPayment, int currentPageNo,
            int pageSize) throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Bill> list = new ArrayList<>();

        if (conn != null) {
            StringBuffer sql = new StringBuffer();
            sql.append("select b.*,p.proName as providerName\n" +
                    "from smbms_bill b,smbms_provider p\n" +
                    "where b.providerId = p.id");

            ArrayList<Object> listBill = new ArrayList<>();
            if (productName != null) {
                sql.append(" and b.productName like ?");
                listBill.add("%" + productName + "%");
            }
            if (providerId != 0) {
                sql.append(" and b.providerId = ?");
                listBill.add(providerId);
            }
            if (isPayment > 0) {
                sql.append(" and b.isPayment = ?");
                listBill.add(isPayment);
            }

            // 添加排序与分页
            sql.append(" order by b.creationDate desc limit ?, ?");
            int startIndex = (currentPageNo - 1) * pageSize;
            listBill.add(startIndex);
            listBill.add(pageSize);

            Object[] params = listBill.toArray();
            System.out.println("BillDaoImpl-->:" + sql.toString());
            rs = BaseDao.execute(conn, ps, rs, sql.toString(), params);
            while (rs.next()) {
                Bill bill = new Bill();
                bill.setId(rs.getInt("id"));
                bill.setBillCode(rs.getString("billCode"));
                bill.setProductName(rs.getString("productName"));
                bill.setProductDesc(rs.getString("productDesc"));
                bill.setProductUnit(rs.getString("productUnit"));
                bill.setProductCount(rs.getBigDecimal("productCount"));
                bill.setTotalPrice(rs.getBigDecimal("totalPrice"));
                bill.setIsPayment(rs.getInt("isPayment"));
                bill.setProviderId(rs.getInt("providerId"));
                bill.setProviderName(rs.getString("providerName"));
                bill.setCreationDate(rs.getTimestamp("creationDate"));
                bill.setCreatedBy(rs.getInt("createdBy"));
                list.add(bill);
            }
            BaseDao.closeResource(null, ps, rs);
        }
        return list;
    }

    // 添加订单
    @Override
    public int addBill(Connection conn, Bill bill) throws SQLException {
        PreparedStatement ps = null;
        int count = 0;
        if (conn != null) {
            String sql = "insert into smbms_bill(billCode, productName,\n" +
                    "                       productDesc, productUnit,\n" +
                    "                       productCount, totalPrice,\n" +
                    "                       isPayment, createdBy,\n" +
                    "                       creationDate,providerId)\n" +
                    "VALUES(?,?,?,?,?,?,?,?,?,?)";
            Object[] params = { bill.getBillCode(), bill.getProductName(), bill.getProductDesc(),
                    bill.getProductUnit(), bill.getProductCount(), bill.getTotalPrice(),
                    bill.getIsPayment(), bill.getCreatedBy(), bill.getCreationDate(),
                    bill.getProviderId() };
            count = BaseDao.execute(conn, ps, sql, params);
        }
        BaseDao.closeResource(null, ps, null);
        return count;
    }

    // 查询订单信息
    @Override
    public Bill getBillId(Connection conn, int id) throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        Bill bill = null;
        if (conn != null) {
            String sql = "select b.*,p.proName as providerName from smbms_bill b,smbms_provider p where b.id = ? and b.providerId = p.id";
            Object[] params = { id };
            rs = BaseDao.execute(conn, ps, rs, sql, params);
            if (rs.next()) {
                bill = new Bill();
                bill.setId(rs.getInt("id"));
                bill.setBillCode(rs.getString("billCode"));
                bill.setProductName(rs.getString("productName"));
                bill.setProductDesc(rs.getString("productDesc"));
                bill.setProductUnit(rs.getString("productUnit"));
                bill.setProductCount(rs.getBigDecimal("productCount"));
                bill.setTotalPrice(rs.getBigDecimal("totalPrice"));
                bill.setIsPayment(rs.getInt("isPayment"));
                bill.setCreatedBy(rs.getInt("createdBy"));
                bill.setCreationDate(rs.getTimestamp("creationDate"));
                bill.setProviderId(rs.getInt("providerId"));
                bill.setProviderName(rs.getString("providerName"));
                bill.setModifyBy(rs.getInt("modifyBy"));
                bill.setModifyDate(rs.getTimestamp("modifyDate"));
            }
            BaseDao.closeResource(null, ps, rs);
        }
        return bill;
    }

    //修改订单
    @Override
    public int updateBill(Connection conn, Bill bill) throws SQLException {
        int updateRows = 0;
        PreparedStatement ps = null;
        if (conn != null) {
            String sql = "update smbms_bill set productName=?,productDesc=?,productUnit=?,productCount=?,totalPrice=?,isPayment=?,modifyBy=?,modifyDate=?,providerId=? where id=?";
            Object[] params = { bill.getProductName(), bill.getProductDesc(),
            bill.getProductUnit(), bill.getProductCount(),bill.getTotalPrice(),
            bill.getIsPayment(),bill.getModifyBy(),bill.getModifyDate(),
            bill.getProviderId(),bill.getId()};
            updateRows = BaseDao.execute(conn, ps, sql, params);
            BaseDao.closeResource(null, ps, null);
        }
        return updateRows;
    }

    @Override
    public Bill getBillCode(Connection conn, String billCode) throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        Bill bill = null;
        if (conn != null)
        {
            String sql = "select * from smbms_bill where billCode = ?";
            Object[] params = { billCode };
            rs = BaseDao.execute(conn, ps, rs, sql, params);
            if (rs.next()) {
                bill = new Bill();
                bill.setId(rs.getInt("id"));
                bill.setBillCode(rs.getString("billCode"));
                bill.setProductName(rs.getString("productName"));
                bill.setProductDesc(rs.getString("productDesc"));
                bill.setProductUnit(rs.getString("productUnit"));
                bill.setProductCount(rs.getBigDecimal("productCount"));
                bill.setTotalPrice(rs.getBigDecimal("totalPrice"));
                bill.setIsPayment(rs.getInt("isPayment"));
                bill.setProviderId(rs.getInt("providerId"));
            }
            BaseDao.closeResource(conn, ps, rs);
        }
        return bill;
    }

    @Override
    public int deleteBill(Connection conn, int id) throws SQLException {
        PreparedStatement ps = null;
        int updateRows = 0;
        if (conn != null) {
            String sql = "delete from smbms_bill where id = ?";
            Object[] params = {id};
            updateRows = BaseDao.execute(conn, ps, sql, params);
        }
        BaseDao.closeResource(null, ps, null);
        return updateRows;
    }
}
